import os
import json

import requests
import MySQLdb

# import aliyunProvince
import pdb
import random
import time
def insert_projects(cursor, js, check = False):
    for project_info in js['data']['projectLists']:
        introduction = project_info['introduction'].replace('\'','\\\'')
        organizationId = project_info['organizationId']
        organizationName = project_info['organizationName'].replace('\'','\\\'')
        projectId = project_info['projectId']
        projectName = project_info['projectName'].replace('\'','\\\'')
        organizationExists = False
        sql_statement = 'select id from organization where id = \'{0}\''.format(organizationId)
        cursor.execute(sql_statement)
        result = cursor.fetchone()
        if(result is not None):
            organizationExists = True
        
        if(check):
            sql_select_statement = 'select id from project where id = \'%s\'' % projectId
            cursor.execute(sql_select_statement)
            result = cursor.fetchone()
            if(result is not None):
                continue
        if not(organizationExists):
            # insert organization first
            sql_statement = 'insert into organization (id, name) values (\'{0}\',\'{1}\')'.format(organizationId, organizationName)
            try:
                cursor.execute(sql_statement)
            except Exception as e:
                print(e)
                pdb.set_trace()            
        sql_statement = 'insert into project (id, name, introduction, organization_id) values (\'{0}\',\'{1}\',\'{2}\',\'{3}\')'.format(
            projectId, projectName, introduction, organizationId
        )
        try:
            cursor.execute(sql_statement)
        except Exception as e:
            print(e)
            pdb.set_trace()
           
if __name__ == '__main__':
    db = MySQLdb.connect(host="localhost", user="volunteer_geek", passwd="volunteer_geek", port=3306, db='volunteer_geek', charset='utf8mb4')
    cursor = db.cursor()
    TOTAL_PAGE = 100
    LIST_PROJECT_URL = 'https://greencode.aliyun.com/project/listProject.htm?spm=5176.11387152.1007916.greencode-block-nav-1.3bbd39adk6ntDB'
    BASE_URL = 'https://greencode.aliyun.com/ajax/projectAjax/ProjectListByPage.json'
    payload = {'projectCategory':'','contentType':'','projectStatus':'','sortWay':'desc', 'keywords':'', 'currentPage': 1,'pageSize':12}
    start_page = 1
    if(os.path.exists('cache/cache_org.txt')):
        file_str = open('cache/cache_org.txt', 'rb').read().decode('utf-8')
        js = json.loads(file_str)
        start_page = js['data']['pageInfo']['currentPage'] + 1
        if(js['code']=='0'):
            insert_projects(cursor, js, check = True)
            db.commit()
    
    s = requests.Session()
    headers = {'user-agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.109 Safari/537.36'}
    r = s.get(LIST_PROJECT_URL, headers=headers)
    headers['referer'] = LIST_PROJECT_URL
    for i in range(start_page, TOTAL_PAGE + 1):
        payload['currentPage'] = i
        r = s.get(BASE_URL, params=payload, headers=headers)
        js = json.loads(r.text)
        if(js['code']!='0'):
            pdb.set_trace()
        open('cache/cache_org.txt','wb').write(r.text.encode('utf-8'))
            
        # save the result to local database        
        insert_projects(cursor, js, check = True)
        db.commit()
        print('finish %d' % i)
        # sleep for random time
        time.sleep(random.random())